<?php

/**
 *	Common methods to every CRUD action, but just for POSTGRES
 */
abstract class Cms_Adapter_Pgsql_Abstract extends Cms_Adapter_Abstract
{
	/**
	 *	Return a list of all columns from the table
	 *	
	 *	@param string $schema
	 *	@param string $table
	 *	@return array
	 */
	public function getColumnsFromTable($column = null)
	{
		$isPrimary = $this->_db->select()
			->from(
				'pg_constraint',
				array('CASE WHEN count(1) = 1 THEN true ELSE false END'),
				'pg_catalog'
			)
			->where('pg_constraint.conrelid = c.oid')
			->where('a.attnum = ANY(pg_constraint.conkey)')
			->where("pg_constraint.contype = 'p'");
		
		$subForeign = $this->_db->select()
			->from(
				array('co3' => 'pg_constraint'),
				array('co3.oid'),
				'pg_catalog'
			)
			->where('co3.conrelid = c.oid')
			->where('a.attnum = ANY(co3.conkey)')
			->where("co3.contype = 'f'");
		
		$foreignKeys = $this->_db->select()
			->from(
				array('co2' => 'pg_constraint'),
				array(new Zend_Db_Expr(
					"n2.nspname||'.'||c2.relname||'.'||a2.attname||' = '||".
					"n.nspname||'.'||c.relname||'.'||a.attname"
				)),
				'pg_catalog'
			)
			->join(
				array('c2' => 'pg_class'),
				'c2.oid = co2.confrelid',
				array(),
				'pg_catalog'
			)
			->join(
				array('n2' => 'pg_namespace'),
				'n2.oid = c2.relnamespace',
				array(),
				'pg_catalog'
			)
			->join(
				array('a2' => 'pg_attribute'),
				'a2.attrelid = c2.oid AND a2.attnum = ANY(co2.confkey)',
				array(),
				'pg_catalog'
			)
			->where("($subForeign) = co2.oid");
		
		$sql = $this->_db->select()
			->from(
				array('a' => 'pg_attribute'),
				array(
					'column_name' => 'attname',
					'type' => 'FORMAT_TYPE(a.atttypid, a.atttypmod)',
					'notnull' => 'attnotnull',
					'is_primary' => "($isPrimary)",
					'reference' => "($foreignKeys)"
				),
				'pg_catalog'
			)
			->join(
				array('c' => 'pg_class'),
				'c.oid = a.attrelid',
				array(),
				'pg_catalog'
			)
			->join(
				array('n' => 'pg_namespace'),
				'c.relnamespace = n.oid',
				array(),
				'pg_catalog'
			)
			->joinLeft(
				array('d' => 'pg_attrdef'),
				"(
					a.attrelid = d.adrelid AND
					a.attnum = d.adnum AND
					d.adsrc LIKE '%nextval(%'
				)",
				array('auto_incr' => 'adsrc'),
				'pg_catalog'
			)
			->where('a.attisdropped = false')
			->where('a.attnum > 0')
			->where('c.relname = ?', $this->getTable(), 'string')
			->where('n.nspname = ?', $this->getSchema(), 'string');
		
		if(!empty($column)) {
			$sql->where('a.attname = ?', $column, 'string');
			
			return $this->_db->fetchRow($sql);
		}
		
		return $this->_db->fetchAll($sql);
	}
	
	/**
	 *	Get all tables that make reference to the table
	 */
	public function getDependentTables()
	{
		$sql = $this->_db->select()
			->from(
				array('con' => 'pg_constraint'),
				array(
					'to' => new Zend_Db_Expr(
						"n.nspname||'.'||c.relname||'.'||a.attname"
					),
					'from' => new Zend_Db_Expr(
						"n2.nspname||'.'||c2.relname||'.'||a2.attname"
					)
				),
				'pg_catalog'
			)
			->join(
				array('c' => 'pg_class'),
				'c.oid = con.confrelid',
				array(),
				'pg_catalog'
			)
			->join(
				array('a' => 'pg_attribute'),
				'a.attrelid = c.oid AND a.attnum = ANY(con.confkey)',
				array(),
				'pg_catalog'
			)
			->join(
				array('n' => 'pg_namespace'),
				'n.oid = c.relnamespace',
				array(),
				'pg_catalog'
			)
			->join(
				array('c2' => 'pg_class'),
				'c2.oid = con.conrelid',
				array(),
				'pg_catalog'
			)
			->join(
				array('a2' => 'pg_attribute'),
				'a2.attrelid = c2.oid AND a2.attnum = ANY(con.conkey)',
				array(),
				'pg_catalog'
			)
			->join(
				array('n2' => 'pg_namespace'),
				'n2.oid = c2.relnamespace',
				array(),
				'pg_catalog'
			)
			->where('c.relname = ?', $this->getTable(), 'string')
			->where('n.nspname = ?', $this->getSchema(), 'string');
		
		return $this->_db->fetchAll($sql);
	}
	
	public function getTableParams($key = null)
	{
		// Get bootstrap object
		$fc = Zend_Controller_Front::getInstance();
		$bootstrap = $fc->getParam('bootstrap');
		
		// if was implemented the method in bootstrap to return the path to the
		// file with the parameters from tables
		if(method_exists($bootstrap, 'getCmsTableParamsFile'))
		{
			$params = new Zend_Config_Ini(
				$bootstrap->getCmsTableParamsFile()
			);
			
			// Try to catch the parameters for the table
			if(isset($params->{$this->getSchema()}->{$this->getTable()}))
			{
				$fields = false;
				
				// Searching for all params
				if(empty($key)) {
					$fields = $params
						->{$this->getSchema()}
						->{$this->getTable()}
						->toArray();
				}
				// If exists the key
				elseif($params->{$this->getSchema()}->{$this->getTable()}->$key)
				{
					$fields = $params
						->{$this->getSchema()}
						->{$this->getTable()}
						->$key
						->toArray();
				}
				return $fields;
			}
		}
		return false;
	}
	
	/**
	 *	Return the column that represent this table to shown and search data
	 */
	public function representativeTableColumn()
	{
		$params = $this->getTableParams();
		
		$field = null;
		
		// Get the first character field from this table
		$columns = $this->getColumnsFromTable();
		
		// The field exists in the params table from comments
		if(isset($params['namefield'])) {
			$field = $params['namefield'];
			
			if($this->getColumnsFromTable($field) === false) {
				throw new Zend_Exception(
					"There is an error dude! The column '$field' do not exist".
					" in the table '{$this->getSchema()}.{$this->getTable()}'"
				);
			}
		} else {
			foreach($columns as $column) {
				if(preg_match('/char/', $column['type'])) {
					$field = $column['column_name'];
					break;
				}
			}
			
			// The table have no one character field
			if(empty($field)) {
				$field = $columns[0]['column_name'];
			}
		}
		return $field;
	}
	
	/**
	 *	Get the primary keys from table
	 */
	public function getPrimaryKey()
	{
		$sql = $this->_db->select()
			->from(
				array('con' => 'pg_constraint'),
				array(),
				'pg_catalog'
			)
			->join(
				array('c' => 'pg_class'),
				'c.oid = con.conrelid',
				array(),
				'pg_catalog'
			)
			->join(
				array('n' => 'pg_namespace'),
				'n.oid = c.relnamespace',
				array(),
				'pg_catalog'
			)
			->join(
				array('a' => 'pg_attribute'),
				'a.attrelid = c.oid AND a.attnum = ANY(con.conkey)',
				array('attname'),
				'pg_catalog'
			)
			->where('c.relname = ?', $this->getTable(), 'string')
			->where('n.nspname = ?', $this->getSchema(), 'string')
			->where("con.contype = 'p'");
		
		return $this->_db->fetchCol($sql);
	}
	
	/**
	 *	Check if the table is a Many to Many table
	 *	@return boolean 
	 */
	public function isManyToMany()
	{
		$params = $this->getTableParams();
		
		if(
			!empty($params) &&
			isset($params['nmtable']) &&
			$params['nmtable'] == true
		) {
			return true;
		}
		
		return false;
	}
	
	/**
	 *	Sum every information about the columns from NM table to mount the html
	 *	form on the view
	 *	
	 *	@param Cms_Abstract $cms
	 *	@param string $localDepField 
	 *	@return array
	 */
	public function nmColsAndViewTableName(Cms_Abstract $cms)
	{
		$result = array('view_table' => 'Múltiplos');
		
		$columns = $cms->getAdapter()->getColumnsFromTable();
		foreach($columns as $key => $column)
		{
			$columns[$key]['view_name'] = $cms->getAdapter()->rename(
				$column['column_name']
			);
			
			// Foreign key
			if(!empty($column['reference']))
			{
				// Explode reference to get tables, schemas and fields of
				// reference
				list($nmTo) = explode(' = ', $column['reference']);
				list(
					$nmToParam['schema'],
					$nmToParam['table'],
					$nmToParam['field']
				) = explode('.', $nmTo);
				
				// Strings to compare to and from tables
				$nmCompare = $nmToParam['schema'].'.'.$nmToParam['table'];
				$localCompare = $this->getSchema().'.'.$this->getTable();

				// If this field is not reference to this own table
				if($nmCompare !== $localCompare)
				{
					// Get list of data from NM table
					$columns[$key]['reference'] = $cms->getAdapter()
						->getForeignList($column['reference']);
				} else {
					unset($columns[$key]);
				}
			}
		}
		$result['columns'] = $columns;
		
		return $result;
	}
	
	public function save($data, $where = null)
	{
		$result = false;
		
		$primary = $this->getPrimaryKey();
		
		// Zend framework obrigates a model to have a primary key setted, but
		// a lot of times in Many to Many tables, this may not have no one. If
		// this is the case then we shall try add the row by a native SQL statement
		if(empty($primary))
		{
			// Prepare columns and params
			$fields = implode(', ', array_keys($data));
			$params = ':'.implode(', :', array_keys($data));
			
			// UPDATE statement
			if(!empty($where)) {
				
				// @TODO: Implementar update manual aqui
				$result = $this->update($data, $where);
				
			// INSERT statement
			} else {
				$sql = 'INSERT INTO '
					.$this->getSchema().'.'
					.$this->getTable()
					.' ('.$fields.')'
					.' VALUES ('.$params.')';
			}
			
			$result = $this->getAdapter()->prepare($sql)->execute($data);
		} else {
			if(!empty($where)) {
				$result = $this->update($data, $where);
			} else {
				$result = $this->insert($data);
			}
		}
		
		return $result;
	}
}